package services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DBConnector {
	private static String url = "jdbc:mysql://localhost/";
	private static String client = "test";
	private static String element = "dataelement";
	private static String alarmtype = "alarmtype";
	private static String alarmclock = "alarmclock";
	private static String name = "root";
	private static String password = "12345678";

	public static Integer addClient(User newUser, String userpassword)
			throws SQLException, ClassNotFoundException {
		Integer retData = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		Class.forName("com.mysql.jdbc.Driver");

		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from user where username = '"
				+ newUser.getName() + "';");
		if (rs.next()) {
			retData = -1;
		} else {

			String query = "insert into user (username,userpassword) values"
					+ " ('" + newUser.getName() + "', '" + userpassword + "');";
			st.executeUpdate(query);
			rs = st.executeQuery("select * from user where username = '"
					+ newUser.getName() + "';");
			if (!rs.next()) {
				retData = -2;

			} else {
				retData = rs.getInt("iduser");
			}

		}
		con.close();
		return retData;
	}

	public static User findClient(String userlogin) throws SQLException,
			ClassNotFoundException {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from user where username = '"
				+ userlogin + "';");

		if (!rs.next()) {
			return new User();
		} else {
			return new User(rs.getString(2), rs.getInt(1));
		}

	}

	public static User findClientById(Integer id) throws SQLException,
			ClassNotFoundException {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from user where iduser = '"
				+ id + "';");

		if (!rs.next()) {
			return new User();
		} else {
			return new User(rs.getString(2), rs.getInt(1));
		}

	}

	public static boolean checkPassword(Integer userid, String userassword)
			throws SQLException, ClassNotFoundException {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from user where iduser = "
				+ userid + " AND userpassword = '" + userassword + "';");

		if (!rs.next()) {
			return false;
		} else {
			return true;
		}

	}

	public static List<UserData> getUserDataById(Integer userid)
			throws SQLException, ClassNotFoundException {
		ArrayList<UserData> data = new ArrayList<UserData>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st
				.executeQuery("select * from " + element+ " where userid = "
						+ userid + ";");
		while (rs.next()) {
			data.add(new UserData(rs.getInt("iddataelement"), rs
					.getInt("userid"), new Date(rs.getTimestamp("dataDate").getTime()), 
					new Date(rs.getTimestamp("startTime").getTime()),
					new Date(rs.getTimestamp("endTime").getTime()),
					rs.getString("note")));
		}
		return data;
	}
	public static List<AlarmType> getAlarmTypes()
			throws SQLException, ClassNotFoundException {
		ArrayList<AlarmType> data = new ArrayList<AlarmType>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st
				.executeQuery("select * from " + alarmtype +  ";");
		while (rs.next()) {
			data.add(new AlarmType(rs.getInt("idalarmtype"), rs.getInt("type"), rs
					.getString("description")));
		}
		return data;
	}

	public static int addUserData(UserData data) throws SQLException,
			ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		String string1 = "insert into "+element+ " (userid,dataDate,starttime,endtime,note) values "
				+ "('"
				+ data.getUserid()
				+ "','"
				+ new Timestamp(data.getDate().getTime())
				+ "','"
				+ new Timestamp(data.getStartTime().getTime())
				+ "','"
				+ new Timestamp(data.getEndTime().getTime())
				+ "','"
				+ data.getNote() + "');";
		st.executeUpdate(string1);
		System.out.println(string1);
		String string = "select * from " + element +" where userid = '"
				+ data.getUserid() + "' AND dataDate = '"
				+ new Timestamp(data.getDate().getTime())
				+ "';";
		System.out.println(string);
		ResultSet executeQuery = st
				.executeQuery(string);
		executeQuery.next();
		return executeQuery.getInt("iddataelement");
	}

	public static void updateUserData(UserData data) throws SQLException,
			ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		String string = "update " + element + " set userid = '" + data.getUserid()
				+ "', dataDate= '"
				+ new Timestamp(data.getDate().getTime())
				+ "', starttime = '"
				+ new Timestamp(data.getStartTime().getTime()) + "', endtime = '"
				+ new Timestamp(data.getEndTime().getTime()) + "', note = '"
				+ data.getNote() + "' where iddataelement = " + data.getId()
				+ ";";
		System.out.println(string);
		st.executeUpdate(string);
	}

	public static void removeUserData(int id) throws SQLException,
			ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		st.executeUpdate("delete from " + element + " where iddataelement = " + id
				+ ";");
	}

	public static List<UserData> getUserDataByIdAndInterval(Integer id,
			Date startDate, Date endDate) throws SQLException,
			ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		ArrayList<UserData> data = new ArrayList<UserData>();
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		StringBuilder builder = new StringBuilder("select * from " + element+ " where userid = '"
				+ id);
		if(startDate != null && endDate != null)
			builder.append("' AND datadate >= '" + new Timestamp(startDate.getTime())).
				append("' AND datadate <= '" + new Timestamp(endDate.getTime()));
		else 
			if(startDate != null)
				builder.append("' AND datadate >= '" + new Timestamp(startDate.getTime()));
			else
				if(endDate != null)
					builder.append("' AND datadate <= '" + new Timestamp(endDate.getTime()));
		builder.append("';");
		System.out.println(builder.toString());
		ResultSet rs = st
				.executeQuery(builder.toString());
		while (rs.next()) {
			data.add(new UserData(rs.getInt("iddataelement"), rs
					.getInt("userid"), new Date(rs.getTimestamp("dataDate").getTime()), 
					new Date(rs.getTimestamp("startTime").getTime()),
					new Date(rs.getTimestamp("endTime").getTime()),
					rs.getString("note")));
		}
		return data;
	}

	public static void addAlarmType(AlarmType type) throws SQLException,
		ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		String string1 = "insert into "+ alarmtype + " (type,description) values "
				+ "('"
				+ type.getType()
				+ "','"
				+ type.getDescription()
				+ "');";
		ResultSet rs = st
				.executeQuery(string1);
	}

	public static List<AlarmClock> getAlarmClocks(User user) 
			throws SQLException, ClassNotFoundException {
		ArrayList<AlarmClock> data = new ArrayList<AlarmClock>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		ResultSet rs = st
				.executeQuery("select * from " + alarmclock + " where userid = "
						+ user.getId() + ";");
		while (rs.next()) {
			Statement st1 = con.createStatement();
			ResultSet rs1 = st1.executeQuery("select * from " + alarmtype + " where idalarmtype = " 
					+ rs.getInt("type") + ";");
			if(!rs1.next())
				continue;
			data.add(new AlarmClock(rs.getInt("idalarmclock"), rs
					.getInt("userid"), new Date(rs.getTimestamp("startDate").getTime()), 
					new Date(rs.getTimestamp("endDate").getTime()), 
					new AlarmType(rs1.getInt("idalarmtype"), rs1.getInt("type"), rs1.getString("description")),
					new Date(rs.getTimestamp("time").getTime())));
		}
		return data;
	}

	public static int addAlarmClock(AlarmClock clock) throws SQLException,
			ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		String string1 = "insert into "+ alarmclock + " (userid,startDate,endDate,type,time) values "
				+ "('"
				+ clock.getUserid()
				+ "','"
				+ new Timestamp(clock.getStartDate().getTime())
				+ "','"
				+ new Timestamp(clock.getEndDate().getTime())
				+ "','"
				+ clock.getType().getId()
				+ "','"
				+ new Timestamp(clock.getTime().getTime()) + "');";
		st.executeUpdate(string1);
		System.out.println(string1);
		String string = "select * from " + alarmclock +" where userid = '"
				+ clock.getUserid() + "' AND startDate = '"
				+ new Timestamp(clock.getStartDate().getTime())
				+ "';";
		System.out.println(string);
		ResultSet executeQuery = st
				.executeQuery(string);
		executeQuery.next();
		return executeQuery.getInt("idalarmclock");
	}

	public static void updateAlarmClock(AlarmClock clock)throws SQLException,
	ClassNotFoundException {
	try {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
	} catch (InstantiationException | IllegalAccessException e) {
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
	
	Class.forName("com.mysql.jdbc.Driver");
	Connection con = DriverManager.getConnection(url + client, name,
			password);
	Statement st = con.createStatement();
	String string = "update " + alarmclock + " set userid = '" + clock.getUserid()
			+ "', startDate= '"
			+ new Timestamp(clock.getStartDate().getTime())
			+ "', endDate = '"
			+ new Timestamp(clock.getEndDate().getTime()) + "', time = '"
			+ new Timestamp(clock.getTime().getTime()) + "', type = '"
			+ clock.getType().getId() + "' where idalarmclock = " + clock.getId()
			+ ";";
	System.out.println(string);
	st.executeUpdate(string);
		
	}

	public static void removeAlarmClock(int id) throws SQLException,
		ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(url + client, name,
				password);
		Statement st = con.createStatement();
		st.executeUpdate("delete from " + alarmclock + " where idalarmclock = " + id
				+ ";");
	}
}
